package jrmob.yonyou.com.commonlib.baseManager;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;

/**
 * 
 * 描述: 数据库管理者
 * 
 * @下午4:11:51
 * @author zhangg
 */
public class DBManager {
	private DBHelper_lib helper;
	private SQLiteDatabase db;




	public DBManager(Context context) {
		helper = new DBHelper_lib(context);
		setDb(helper.getWritableDatabase());
	}


	/**
	 * 插入我审批的单据
	 * @param billid
	 * @param billno
	 * @param billtype
	 * @param billtypename
	 * @param procresult
	 * @param taskid
	 * @param ts
	 * @param unitshortname
	 * @param user_name
	 */
	public void insertMyApproveBills(String billid, String userid, String billno,
									 String billtype, String billtypename,
									 String procresult, String taskid,
									 String ts, String unitshortname,
									 String user_name,String isback) {

		String sql = "insert into " + DBHelper_lib.myApproveBills
				+ "(billid,userid,billno,billtype,billtypename,procresult,taskid,ts,unitshortname,user_name,isback) " +
				"values ('" + billid + "','" + userid + "','" + billno + "','" + billtype + "','" +
				billtypename + "','" +
				procresult + "','" +
				taskid + "','" +
				ts + "','" +
				unitshortname + "','" +
				user_name + "','" +
				isback +"')";

		helper.getWritableDatabase().execSQL(sql);

	}

	/**
	 * 删除重复数据保留id最大一条
	 * @param billno
	 */
	public void deleteMyApproveBills(String billno) {

		//"delete from Yookey where tit not in (select min(tit) from Yookey group by SID)";

		String sql = "DELETE From "+ DBHelper_lib.myApproveBills +
				" WHERE _id  NOT IN (SELECT MAX(_id) FROM "+
				DBHelper_lib.myApproveBills +" GROUP BY '"+ billno +"')";

		helper.getWritableDatabase().execSQL(sql);

	}

	/**
	 * 删除该条数据
	 * @param billno
	 */
	public void deleteBills(String billno) {

		String sql = "DELETE FROM "+ DBHelper_lib.myApproveBills
				+" WHERE billno = '" + billno + "'";

		helper.getWritableDatabase().execSQL(sql);

	}
	/**
	 * 删除该条数据
	 * @param billno
	 */
	public int queryBills(String billno) {
		//SELECT * FROM emp WHERE job='CLERK';
		String sql = "SELECT * FROM "+ DBHelper_lib.myApproveBills
				+" WHERE billno = '" + billno + "'";
		Cursor c = helper.getWritableDatabase().rawQuery(sql, null);
		return c.getCount();
		//helper.getWritableDatabase().execSQL(sql);
	}


	/**
	 * 根据用户id查询我已审批的单据
	 * @param userid
	 * @return
	 */
	public Cursor queryMyApproveBills(String userid) {

		// 创建表(如果不存在的话)
		String sql = "create table if not exists " + DBHelper_lib.myApproveBills
				+ "(_id integer primary key autoincrement "
				+ ", billid varchar(30)"
				+ ", userid varchar(30)"
				+ ", billno varchar(30)"
				+ ", billtype varchar(30)"
				+ ", billtypename varchar(30)"
				+ ", procresult varchar(30)"
				+ ", taskid varchar(30)"
				+ ", ts varchar(30)"
				+ ", unitshortname varchar(30)"
				+ ", user_name varchar(20))";
		helper.getWritableDatabase().execSQL(sql);

		Cursor c = helper.getWritableDatabase().
				rawQuery("SELECT * FROM " + DBHelper_lib.myApproveBills +
						" where userid ='"+userid+"' "+
						" ORDER BY _id DESC ", null);

		return c;

	}



	/**
	 * close database
	 */
	public void closeDB() {
		getDb().close();
	}

	public SQLiteDatabase getDb() {
		return db;
	}

	public void setDb(SQLiteDatabase db) {
		this.db = db;
	}
}